/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accountproject.data;

import accountproject.model.Customer;
import accountproject.model.Detail;
import accountproject.model.OfferObject;
import accountproject.model.Work;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Amon
 */
public class DataIO {

    private Connection con;

    public DataIO() {
        try {
            if (con == null) {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                con = DriverManager.getConnection("jdbc:mysql://localhost/accounting", "master", "master");

                if (!con.isClosed()) {
                    System.out.print("Connection created");
                }

            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataIO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DataIO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(DataIO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(DataIO.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    public int insertUser(Customer customer) throws SQLException {
        Statement stmt = con.createStatement();
        String sql = "INSERT INTO customer VALUES(null," + customer.getFieldsAsString() + ")";

        //System.out.println(sql);
        return stmt.executeUpdate(sql);

    }

    public int insertWork(Work work) throws SQLException {
        Statement stmt = con.createStatement();
        String sql = "INSERT INTO works VALUES(null," + work.getFieldsAsString() + ")";

        //System.out.println(sql);
        return stmt.executeUpdate(sql);
    }

    public ResultSet getCustomers() throws SQLException {
        Statement stmt = con.createStatement();
        String sql = "SELECT * FROM customer";

        return stmt.executeQuery(sql);
    }

    public ResultSet getWorks() throws SQLException {
        Statement stmt = con.createStatement();
        String sql = "SELECT * FROM works";
        return stmt.executeQuery(sql);
    }

    public void saveOffer(OfferObject offerObject) {
        String accountOfferSql = "INSERT INTO account_offer VALUES(null,?,?,?,null)";
        String detailSql = "INSERT INTO detail VALUES(null, ?,?,?,?,?)";
        try {
            con.setAutoCommit(false);
            PreparedStatement offerPs = con.prepareStatement(accountOfferSql);
            PreparedStatement detailPs = con.prepareStatement(detailSql);
            offerPs.setString(1, offerObject.getOfferId());
            offerPs.setString(2, "O");
            offerPs.setInt(3, offerObject.getCustomerId());

            int id = 0;
            if (offerPs.executeUpdate() == 1) {
                String idSql = "SELECT ID FROM account_offer WHERE ITEM_NR='" + offerObject.getOfferId() + "'";
                ResultSet newRowRs = con.createStatement().executeQuery(idSql);
                if (newRowRs.next()) {
                    id = newRowRs.getInt("ID");
                }
                for (Detail detail : offerObject.getDetails()) {
                    detailPs.setInt(1, id);
                    detailPs.setInt(2, detail.getWorkId());
                    detailPs.setInt(3, detail.getAmount());
                    detailPs.setDouble(4, detail.getUnitPrice());
                    detailPs.setDouble(5, detail.getDiscount());
                    detailPs.addBatch();
                }
                detailPs.executeBatch();
            }
            con.commit();
            con.setAutoCommit(true);
        } catch (SQLException ex) {
            Logger.getLogger(DataIO.class.getName()).log(Level.SEVERE, null, ex);
            if (con != null) {
                try {
                    System.err.print("Transaction is being ");
                    System.err.println("rolled back");
                    con.rollback();
                } catch (SQLException excep) {
                    System.err.print("SQLException: ");
                    System.err.println(excep.getMessage());
                    Logger.getLogger(DataIO.class.getName()).log(Level.SEVERE, null, excep);

                }
            }
        }
    }
}
